package com.icss.daos;
/*
 * 论坛用户dao类
 * 管志春
 * 2010.03.31
 * 
 * */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.icss.beans.bbsUserVO;
import com.icss.utils.DB;

public class bbsUserDao {

	private Statement stat = null;

	private Connection conn = null;

	private ResultSet rs = null;
	
	//获取所有用户的信息
	public ArrayList<bbsUserVO> getAllUser() {

		ArrayList<bbsUserVO> bbsUservos = new ArrayList<bbsUserVO>();
		conn = DB.getConnection();
		String sql = "select * from BBSUser";

		try {

			stat = conn.createStatement();
			rs = stat.executeQuery(sql);

			while (rs.next()) {
				bbsUserVO bbsUser = new bbsUserVO();
				bbsUser.setBid(rs.getInt("bid"));
				bbsUser.setPid(rs.getInt("pid"));
				bbsUser.setUserName(rs.getString("userName"));
				bbsUser.setUserPwd(rs.getString("userPwd"));
				bbsUser.setSex(rs.getString("sex"));
				bbsUser.setRegisterTime(rs.getDate("registerTime"));
				bbsUser.setAddress(rs.getString("address"));
				bbsUser.setEmail(rs.getString("email"));
				bbsUser.setBirthday(rs.getDate("birthday"));
				bbsUser.setPhone(rs.getString("phone"));
				bbsUser.setMSN(rs.getString("MSN"));
				bbsUser.setFace(rs.getString("face"));

				bbsUservos.add(bbsUser);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return bbsUservos;

	}
	
	//根据id查询用户
	public bbsUserVO getBBSUserById(int bid) {
		//ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();

		conn = DB.getConnection();
		bbsUserVO bbsUser = new bbsUserVO();
		String sql = "select * from BBSUser where bid=" + bid;
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				bbsUser.setBid(rs.getInt("bid"));
				bbsUser.setPid(rs.getInt("pid"));
				bbsUser.setUserName(rs.getString("userName"));
				bbsUser.setUserPwd(rs.getString("userPwd"));
				bbsUser.setSex(rs.getString("sex"));
				bbsUser.setRegisterTime(rs.getDate("registerTime"));
				bbsUser.setAddress(rs.getString("address"));
				bbsUser.setEmail(rs.getString("email"));
				bbsUser.setBirthday(rs.getDate("birthday"));
				bbsUser.setPhone(rs.getString("phone"));
				bbsUser.setMSN(rs.getString("MSN"));
				bbsUser.setFace(rs.getString("face"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return bbsUser;
	}
	
	//根据用户名查找用户
	public bbsUserVO getBBSUserByuserName(String name) {
		//ArrayList<ArticleVO> articlevos = new ArrayList<ArticleVO>();

		conn = DB.getConnection();
		bbsUserVO bbsUser = new bbsUserVO();
		//内联查询
		//String sql = "select * from BBSUser where userName="+name;
		String sql = "select * from BBSUser inner join Article on BBSUser.bid=Article.bid where userName='" + name
				+ "'";
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				bbsUser.setBid(rs.getInt("bid"));
				bbsUser.setPid(rs.getInt("pid"));
				bbsUser.setUserName(rs.getString("userName"));
				bbsUser.setUserPwd(rs.getString("userPwd"));
				bbsUser.setSex(rs.getString("sex"));
				bbsUser.setRegisterTime(rs.getDate("registerTime"));
				bbsUser.setAddress(rs.getString("address"));
				bbsUser.setEmail(rs.getString("email"));
				bbsUser.setBirthday(rs.getDate("birthday"));
				bbsUser.setPhone(rs.getString("phone"));
				bbsUser.setMSN(rs.getString("MSN"));
				bbsUser.setFace(rs.getString("face"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return bbsUser;
	}
	
	//获取全部用户数
	public int getAllUserNum() {

		int result = 0;
		conn = DB.getConnection();
		String sql = "select count(*) as num from BBSUser";
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				result = rs.getInt("num");
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

		close();
		//TODO 刚刚添加了个关闭方法
		return result;

	}

	
	//执行SQL语句
	public boolean executeSQL(String sql) {

		int result = 0;
		conn = DB.getConnection();
		try {
			stat = conn.createStatement();
			result = stat.executeUpdate(sql);
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}

		if (result != 0) {
			return true;
		}

		close();
		//TODO 刚刚添加了个关闭方法
		return false;

	}
	
	
	public ResultSet query(String sql) {

		try {
			rs = stat.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stat.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;

	}
	
	//用户登录查询
	public bbsUserVO bbsLogin(String userName, String password) {

		String sql = "select * from BBSUser where userName='" + userName + "' and userPwd = '" + password + "'";

		Dao dao = new Dao();
		
		bbsUserVO bbsUser = new bbsUserVO();
		
		rs = dao.query(sql);
		
		try {
			if (rs.next()) {
				
				bbsUser.setBid(rs.getInt("bid"));
				bbsUser.setPid(rs.getInt("pid"));
				bbsUser.setUserName(rs.getString("userName"));
				bbsUser.setUserPwd(rs.getString("userPwd"));
				bbsUser.setSex(rs.getString("sex"));
				bbsUser.setRegisterTime(rs.getDate("registerTime"));
				bbsUser.setAddress(rs.getString("address"));
				bbsUser.setEmail(rs.getString("email"));
				bbsUser.setBirthday(rs.getDate("birthday"));
				bbsUser.setPhone(rs.getString("phone"));
				bbsUser.setMSN(rs.getString("MSN"));
				bbsUser.setFace(rs.getString("face"));
				dao.close();

				return bbsUser;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			dao.close();
			e.printStackTrace();
		}
		close();
		return null;
	}

	public void close() {

		try {

			if (null != conn) {
				conn.close();
			}

			if (null != stat) {
				stat.close();
			}

			if (null != rs) {
				rs.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}
	}

}
